对于每个UPDATE，INSERTElsewhere存储过程-ForEachUPDATE,INSERTElsewhereStoredProcedure

It may be the case this is only possible through looping, but who knows.

可能是这种情况只能通过循环来实现,但谁知道呢。

I'm in a Java coding environment using MS SQL Server, and I have been searching around and can't seem to find a good way to INSERT records in say a transaction history table that we must keep updated for every action done in other tables upon issuing a single UPDATE command to UPDATE many records. For each UPDATE record updated, I would like to do an insert statement as well Essentially this is what we currently do, one by one, in code not using a stored procedure:

我在使用MS SQL Server的Java编码环境中,我一直在搜索并且似乎找不到一个很好的方法来插入记录,比如说一个事务历史表,我们必须为其他表中的每个操作保持更新在发出单个UPDATE命令来更新许多记录时。对于更新的每个UPDATE记录,我想做一个插入语句本质上这是我们目前在不使用存储过程的代码中一个接一个地做的事情:

UPDATE table SET column1=null, column2=null, column3=0, column4=getDate() where column5=? and column6=1
...
//Insert transtable
createTransaction(tran, localConn);
...
INSERT INTO transtable ( a, b, c, d, e, f, g, h, i, " +
                 "j, k, l, m, n, o, p, q, r, s, " +
                 "t, u, v, w) " +
                 " VALUES ( ? ,? ,? ,? ,? ,? ,getDate() ,getDate() ,getDate() ,? ,? ,? ,? ,? ,? ,? ,getDate() ,getDate(),? ,? ,? ,?,? )

What I would like to do, is something like this instead for the UPDATE:

我想做的是这样的事情而不是UPDATE:

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[setFlags]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE table1
SET column1 = '2', column2 = NULL
WHERE column1 = '1' or column1 = '2'
--FOR EACH UPDATED ROW IN THIS TABLE, CREATE A TRANSACTION IN TRANSTABLE

UPDATE table2
SET column1 = '0', column2 = NULL
WHERE column1 = '1'
--FOR EACH UPDATED ROW IN THIS TABLE, CREATE A TRANSACTION IN TRANSTABLE

UPDATE table3
SET column1 = '0', column2 = NULL
WHERE column1 = '1'
--FOR EACH UPDATED ROW IN THIS TABLE, CREATE A TRANSACTION IN TRANSTABLE

END

However, for each record updated (usually about ~100 records would be updated using this utility), about 100 INSERTs need to be done as well in our transtable table, which will also contain some of the data from the rows being updated.

但是,对于每个更新的记录(通常使用此实用程序将更新约100条记录),在我们的transtable表中也需要完成大约100个INSERT,它还将包含正在更新的行中的一些数据。

Does anyone have any ideas? Resources out there? Etc.? I'm somewhat of a beginner, so sometimes I just don't know what to search for. Lately I've been searching "for each UPDATE INSERT SQL" or "looping insert for each update"

有没有人有任何想法?有资源吗?等等。?我有点像初学者,所以有时我只是不知道要搜索什么。最近我一直在搜索“每个UPDATE INSERT SQL”或“每个更新的循环插入”

I might need to do WHILE TSQL, but if there is a better way (it also sounds terribly inefficient), that'd be great to know: http://msdn.microsoft.com/en-us/library/ms178642.aspx

我可能需要做TSILE,但如果有更好的方法(它听起来也非常低效),那就太棒了:http://msdn.microsoft.com/en-us/library/ms178642.aspx

2 个解决方案

USE tempdb;
GO
CREATE TABLE mydatanow (id SMALLINT, c1 VARCHAR(10), c2 VARCHAR(10));
CREATE TABLE mydatalog (id SMALLINT, c1 VARCHAR(10), c2 VARCHAR(10), moment DATETIME);
GO
CREATE TRIGGER makelog ON mydatanow
AFTER INSERT, UPDATE
AS BEGIN
    INSERT mydatalog(id, c1, c2, moment)
    SELECT id, c1, c2, GETDATE()
    FROM inserted
END
GO
INSERT mydatanow VALUES (1, 'abe', 'apple');
INSERT mydatanow VALUES (2, 'beth', 'banana');
UPDATE mydatanow SET c1 = 'carl', c2 = 'no fruit';
GO
SELECT * FROM mydatanow;
-- id     c1         c2
-- ------ ---------- ----------
-- 1      carl       no fruit
-- 2      carl       no fruit

SELECT * FROM mydatalog;
-- id     c1         c2         moment
-- ------ ---------- ---------- -----------------------
-- 1      abe        apple      2012-05-10 19:36:45.843
-- 2      beth       banana     2012-05-10 19:36:45.843
-- 1      carl       no fruit   2012-05-10 19:36:45.847
-- 2      carl       no fruit   2012-05-10 19:36:45.847